*------------------------------------------------------------------------------------------------------------*
*Programmed by:				Emma Hedvig Pind Hansen
*Programmed for:			Mette Foged, Linea Hasager, Vasil Yasenov
*Project:					Meta-analysis
*Description:				1. Changes excel-spreadsheet with OECD Data on institutions to paneldata
*							2. Creates a second panel that only contains years matched to the database of estimates
*							3. Creates a panel that only contains countries matched to our database, but all years
*------------------------------------------------------------------------------------------------------------*


************
***Part 1***
************
set more off
clear all


*collbarg
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("CollBarg")


label var A "country"

drop in 1
drop in 1


drop B

rename C C1960
rename D C1961
rename E C1962
rename F C1963
rename G C1964
rename H C1965
rename I C1966
rename J C1967
rename K C1968
rename L C1969
rename M C1970
rename N C1971
rename O C1972
rename P C1973
rename Q C1974
rename R C1975
rename S C1976
rename T C1977
rename U C1978
rename V C1979
rename W C1980
rename X C1981
rename Y C1982
rename Z C1983
rename AA C1984
rename AB C1985
rename AC C1986
rename AD C1987
rename AE C1988
rename AF C1989
rename AG C1990
rename AH C1991
rename AI C1992
rename AJ C1993
rename AK C1994
rename AL C1995
rename AM C1996
rename AN C1997
rename AO C1998
rename AP C1999
rename AQ C2000
rename AR C2001
rename AS C2002
rename AT C2003
rename AU C2004
rename AV C2005
rename AW C2006
rename AX C2007
rename AY C2008
rename AZ C2009
rename BA C2010
rename BB C2011
rename BC C2012
rename BD C2013
rename BE C2014
rename BF C2015
rename BG C2016

reshape long C, i(A) j(year)

rename C CollBarg

tempfile CollBarg
save `CollBarg'

clear all

*strict emp coll
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("StrictEmpColl")

label var A "country"

rename B C1998
rename C C1999
rename D C2000
rename E C2001
rename F C2002
rename G C2003
rename H C2004
rename I C2005
rename J C2006
rename K C2007
rename L C2008
rename M C2009
rename N C2010
rename O C2011
rename P C2012
rename Q C2013
rename R C2014
rename S C2015

drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C StrictEmpColl

tempfile StrictEmpColl
save `StrictEmpColl'

clear all

*strict emp ind coll
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("StrictEmpIndColl")

label var A "country"

rename B C2008
rename C C2009
rename D C2010
rename E C2011
rename F C2012
rename G C2013
rename H C2014
rename I C2015

drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C StrictEmpIndColl

tempfile StrictEmpIndColl
save `StrictEmpIndColl'

clear all

*strict emp ind
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("StrictEmpInd")

label var A "country"

rename B C1985
rename C C1986
rename D C1987
rename E C1988
rename F C1989
rename G C1990
rename H C1991
rename I C1992
rename J C1993
rename K C1994
rename L C1995
rename M C1996
rename N C1997
rename O C1998
rename P C1999
rename Q C2000
rename R C2001
rename S C2002
rename T C2003
rename U C2004
rename V C2005
rename W C2006
rename X C2007
rename Y C2008
rename Z C2009
rename AA C2010
rename AB C2011
rename AC C2012
rename AD C2013
rename AE C2014
rename AF C2015

drop in 1
drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C StrictEmpInd

tempfile StrictEmpInd
save `StrictEmpInd'

clear all

*strict emp temp
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("StrictEmpTemp")

label var A "country"

rename B C1985
rename C C1986
rename D C1987
rename E C1988
rename F C1989
rename G C1990
rename H C1991
rename I C1992
rename J C1993
rename K C1994
rename L C1995
rename M C1996
rename N C1997
rename O C1998
rename P C1999
rename Q C2000
rename R C2001
rename S C2002
rename T C2003
rename U C2004
rename V C2005
rename W C2006
rename X C2007
rename Y C2008
rename Z C2009
rename AA C2010
rename AB C2011
rename AC C2012
rename AD C2013
rename AE C2014
rename AF C2015

drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C StrictEmpTemp

tempfile StrictEmpTemp
save `StrictEmpTemp'

clear all

*strict trade union dens
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("TradeUnionDens")

label var A "country"

drop B

rename C C1960
rename D C1961
rename E C1962
rename F C1963
rename G C1964
rename H C1965
rename I C1966
rename J C1967
rename K C1968
rename L C1969
rename M C1970
rename N C1971
rename O C1972
rename P C1973
rename Q C1974
rename R C1975
rename S C1976
rename T C1977
rename U C1978
rename V C1979
rename W C1980
rename X C1981
rename Y C1982
rename Z C1983
rename AA C1984
rename AB C1985
rename AC C1986
rename AD C1987
rename AE C1988
rename AF C1989
rename AG C1990
rename AH C1991
rename AI C1992
rename AJ C1993
rename AK C1994
rename AL C1995
rename AM C1996
rename AN C1997
rename AO C1998
rename AP C1999
rename AQ C2000
rename AR C2001
rename AS C2002
rename AT C2003
rename AU C2004
rename AV C2005
rename AW C2006
rename AX C2007
rename AY C2008
rename AZ C2009
rename BA C2010
rename BB C2011
rename BC C2012
rename BD C2013
rename BE C2014
rename BF C2015
rename BG C2016

drop in 1
drop in 1
drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C TradeUnionDens

tempfile TradeUnionDens
save `TradeUnionDens'

clear all

*min to median wage
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("MintoMedianWage")

label var A "country"

rename B C1960
rename C C1961
rename D C1962
rename E C1963
rename F C1964
rename G C1965
rename H C1966
rename I C1967
rename J C1968
rename K C1969
rename L C1970
rename M C1971
rename N C1972
rename O C1973
rename P C1974
rename Q C1975
rename R C1976
rename S C1977
rename T C1978
rename U C1979
rename V C1980
rename W C1981
rename X C1982
rename Y C1983
rename Z C1984
rename AA C1985
rename AB C1986
rename AC C1987
rename AD C1988
rename AE C1989
rename AF C1990
rename AG C1991
rename AH C1992
rename AI C1993
rename AJ C1994
rename AK C1995
rename AL C1996
rename AM C1997
rename AN C1998
rename AO C1999
rename AP C2000
rename AQ C2001
rename AR C2002
rename AS C2003
rename AT C2004
rename AU C2005
rename AV C2006
rename AW C2007
rename AX C2008
rename AY C2009
rename AZ C2010
rename BA C2011
rename BB C2012
rename BC C2013
rename BD C2014
rename BE C2015
rename BF C2016

drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C MintoMedianWage

tempfile MintoMedianWage
save `MintoMedianWage'

clear all

*min to mean wage
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("MinToMeanWage")

label var A "country"

rename B C1960
rename C C1961
rename D C1962
rename E C1963
rename F C1964
rename G C1965
rename H C1966
rename I C1967
rename J C1968
rename K C1969
rename L C1970
rename M C1971
rename N C1972
rename O C1973
rename P C1974
rename Q C1975
rename R C1976
rename S C1977
rename T C1978
rename U C1979
rename V C1980
rename W C1981
rename X C1982
rename Y C1983
rename Z C1984
rename AA C1985
rename AB C1986
rename AC C1987
rename AD C1988
rename AE C1989
rename AF C1990
rename AG C1991
rename AH C1992
rename AI C1993
rename AJ C1994
rename AK C1995
rename AL C1996
rename AM C1997
rename AN C1998
rename AO C1999
rename AP C2000
rename AQ C2001
rename AR C2002
rename AS C2003
rename AT C2004
rename AU C2005
rename AV C2006
rename AW C2007
rename AX C2008
rename AY C2009
rename AZ C2010
rename BA C2011
rename BB C2012
rename BC C2013
rename BD C2014
rename BE C2015
rename BF C2016

drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C MinToMeanWage

tempfile MinToMeanWage
save `MinToMeanWage'

clear all

*unemployment
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("Unemployment")

label var A "country"

rename B C1960
rename C C1961
rename D C1962
rename E C1963
rename F C1964
rename G C1965
rename H C1966
rename I C1967
rename J C1968
rename K C1969
rename L C1970
rename M C1971
rename N C1972
rename O C1973
rename P C1974
rename Q C1975
rename R C1976
rename S C1977
rename T C1978
rename U C1979
rename V C1980
rename W C1981
rename X C1982
rename Y C1983
rename Z C1984
rename AA C1985
rename AB C1986
rename AC C1987
rename AD C1988
rename AE C1989
rename AF C1990
rename AG C1991
rename AH C1992
rename AI C1993
rename AJ C1994
rename AK C1995
rename AL C1996
rename AM C1997
rename AN C1998
rename AO C1999
rename AP C2000
rename AQ C2001
rename AR C2002
rename AS C2003
rename AT C2004
rename AU C2005
rename AV C2006
rename AW C2007
rename AX C2008
rename AY C2009
rename AZ C2010
rename BA C2011
rename BB C2012
rename BC C2013
rename BD C2014
rename BE C2015
rename BF C2016

drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 46

reshape long C, i(A) j(year)

rename C Unemployment

tempfile Unemployment
save `Unemployment'

clear all

*gdp growth
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("GDPgrowth")

label var A "country"

rename B C1951
rename C C1952
rename D C1953
rename E C1954
rename F C1955
rename G C1956
rename H C1957
rename I C1958
rename J C1959
rename K C1960
rename L C1961
rename M C1962
rename N C1963
rename O C1964
rename P C1965
rename Q C1966
rename R C1967
rename S C1968
rename T C1969
rename U C1970
rename V C1971
rename W C1972
rename X C1973
rename Y C1974
rename Z C1975
rename AA C1976
rename AB C1977
rename AC C1978
rename AD C1979
rename AE C1980
rename AF C1981
rename AG C1982
rename AH C1983
rename AI C1984
rename AJ C1985
rename AK C1986
rename AL C1987
rename AM C1988
rename AN C1989
rename AO C1990
rename AP C1991
rename AQ C1992
rename AR C1993
rename AS C1994
rename AT C1995
rename AU C1996
rename AV C1997
rename AW C1998
rename AX C1999
rename AY C2000
rename AZ C2001
rename BA C2002
rename BB C2003
rename BC C2004
rename BD C2005
rename BE C2006
rename BF C2007
rename BG C2008
rename BH C2009
rename BI C2010
rename BJ C2011
rename BK C2012
rename BL C2013
rename BM C2014
rename BN C2015
rename BO C2016
rename BP C2017

drop in 1
drop in 1
drop in 1
drop in 60

reshape long C, i(A) j(year)

rename C GDPgrowth

tempfile GDPgrowth
save `GDPgrowth'

clear all


*output gap
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("OutputGap")

label var A "country"

rename B C1985
rename C C1986
rename D C1987
rename E C1988
rename F C1989
rename G C1990
rename H C1991
rename I C1992
rename J C1993
rename K C1994
rename L C1995
rename M C1996
rename N C1997
rename O C1998
rename P C1999
rename Q C2000
rename R C2001
rename S C2002
rename T C2003
rename U C2004
rename V C2005
rename W C2006
rename X C2007
rename Y C2008
rename Z C2009
rename AA C2010
rename AB C2011
rename AC C2012
rename AD C2013
rename AE C2014
rename AF C2015
rename AG C2016
rename AH C2017
rename AI C2018
rename AJ C2019


drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 41

reshape long C, i(A) j(year)

rename C OutputGap

tempfile OutputGap
save `OutputGap'

clear all

*almp exp
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("ALMPExp")

label var A "country"

rename B C1985
rename C C1986
rename D C1987
rename E C1988
rename F C1989
rename G C1990
rename H C1991
rename I C1992
rename J C1993
rename K C1994
rename L C1995
rename M C1996
rename N C1997
rename O C1998
rename P C1999
rename Q C2000
rename R C2001
rename S C2002
rename T C2003
rename U C2004
rename V C2005
rename W C2006
rename X C2007
rename Y C2008
rename Z C2009
rename AA C2010
rename AB C2011
rename AC C2012
rename AD C2013
rename AE C2014
rename AF C2015

drop AG-AM

drop in 1
drop in 1
drop in 1
drop in 1

reshape long C, i(A) j(year)

rename C ALMPExp

tempfile ALMPExp
save `ALMPExp'

clear all

*bte
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("BTE")

drop in 1
drop in 1
drop in 1
drop in 1
drop in 51

label var A "country"

rename B C1998
rename C C2003
rename D C2008
rename E C2013

reshape long C, i(A) j(year)

rename C BTE

tempfile BTE
save `BTE'

clear all

*btti
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("BTTI")

drop in 1
drop in 1
drop in 1
drop in 1
drop in 51

label var A "country"

rename B C1998
rename C C2003
rename D C2008
rename E C2013

reshape long C, i(A) j(year)

rename C BTTI

tempfile BTTI
save `BTTI'

clear all

*state control
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("StateControl")

drop in 1
drop in 1
drop in 1
drop in 1
drop in 51

label var A "country"

rename B C1998
rename C C2003
rename D C2008
rename E C2013

reshape long C, i(A) j(year)

rename C StateControl

tempfile StateControl
save `StateControl'

clear all

*pmr
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("PMR")

drop in 1
drop in 1
drop in 1
drop in 1
drop in 51

label var A "country"

rename B C1998
rename C C2003
rename D C2008
rename E C2013

reshape long C, i(A) j(year)

rename C PMR

tempfile PMR
save `PMR'

clear all


*netreplacement rate

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2015")

*keep families with two children, two-earner married couple and 67% of AW
keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2015

tempfile 2015
save `2015'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2014")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2014

tempfile 2014
save `2014'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2013")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2013

tempfile 2013
save `2013'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2012")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2012

tempfile 2012
save `2012'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2011")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2011

tempfile 2011
save `2011'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2010")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2010

tempfile 2010
save `2010'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2009")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2009

tempfile 2009
save `2009'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2008")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2008

tempfile 2008
save `2008'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2007")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2007

tempfile 2007
save `2007'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2006")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2006

tempfile 2006
save `2006'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2005")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2005

tempfile 2005
save `2005'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2004")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2004

tempfile 2004
save `2004'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2003")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2003

tempfile 2003
save `2003'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2002")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2002

tempfile 2002
save `2002'

clear all

import excel "$data\NetReplacementRate initial unemployment.xlsx", sheet("2001")

keep A G

drop if missing(A)
drop if missing(G)
split(A),parse(*)
keep G A1
rename A1 A

label var A "country"
label var G "Two-earner family replacement rate"

rename G C2001

merge 1:1 A using `2002'
drop _merge

merge 1:1 A using `2003'
drop _merge

merge 1:1 A using `2004'
drop _merge

merge 1:1 A using `2005'
drop _merge

merge 1:1 A using `2006'
drop _merge

merge 1:1 A using `2007'
drop _merge

merge 1:1 A using `2008'
drop _merge

merge 1:1 A using `2009'
drop _merge

merge 1:1 A using `2010'
drop _merge

merge 1:1 A using `2011'
drop _merge

merge 1:1 A using `2012'
drop _merge

merge 1:1 A using `2013'
drop _merge

merge 1:1 A using `2014'
drop _merge

merge 1:1 A using `2015'
drop _merge

reshape long C, i(A) j(year)

rename C NetReplacementRate

tempfile netreplacementrate
save `netreplacementrate'

clear all



*population
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("population")

label var A "country"

rename B C1960
rename C C1961
rename D C1962
rename E C1963
rename F C1964
rename G C1965
rename H C1966
rename I C1967
rename J C1968
rename K C1969
rename L C1970
rename M C1971
rename N C1972
rename O C1973
rename P C1974
rename Q C1975
rename R C1976
rename S C1977
rename T C1978
rename U C1979
rename V C1980
rename W C1981
rename X C1982
rename Y C1983
rename Z C1984
rename AA C1985
rename AB C1986
rename AC C1987
rename AD C1988
rename AE C1989
rename AF C1990
rename AG C1991
rename AH C1992
rename AI C1993
rename AJ C1994
rename AK C1995
rename AL C1996
rename AM C1997
rename AN C1998
rename AO C1999
rename AP C2000
rename AQ C2001
rename AR C2002
rename AS C2003
rename AT C2004
rename AU C2005
rename AV C2006
rename AW C2007
rename AX C2008
rename AY C2009
rename AZ C2010
rename BA C2011
rename BB C2012
rename BC C2013
rename BD C2014
rename BE C2015
rename BF C2016
rename BG C2017
rename BH C2018
rename BI C2019



drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 58

reshape long C, i(A) j(year)

rename C population

tempfile population
save `population'

clear all

*average tenure
import excel "$data\OECD_Data_on_institutions.xlsx", sheet("AverageTenure")

label var A "country"

drop B C

rename D C1983
rename E C1984
rename F C1985
rename G C1986
rename H C1987
rename I C1988
rename J C1989
rename K C1990
rename L C1991
rename M C1992
rename N C1993
rename O C1994
rename P C1995
rename Q C1996
rename R C1997
rename S C1998
rename T C1999
rename U C2000
rename V C2001
rename W C2002
rename X C2003
rename Y C2004
rename Z C2005
rename AA C2006
rename AB C2007
rename AC C2008
rename AD C2009
rename AE C2010
rename AF C2011
rename AG C2012
rename AH C2013
rename AI C2014
rename AJ C2015
rename AK C2016

drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 1
drop in 42
drop in 42

reshape long C, i(A) j(year)

rename C AverageTenure

merge 1:1 A year using `CollBarg', nogen
merge 1:1 A year using `StrictEmpColl', nogen
merge 1:1 A year using `StrictEmpIndColl', nogen
merge 1:1 A year using `StrictEmpInd', nogen
merge 1:1 A year using `StrictEmpTemp', nogen
merge 1:1 A year using `TradeUnionDens', nogen
merge 1:1 A year using `MintoMedianWage', nogen
merge 1:1 A year using `MinToMeanWage', nogen
merge 1:1 A year using `Unemployment', nogen
merge 1:1 A year using `GDPgrowth', nogen
merge 1:1 A year using `ALMPExp', nogen
merge 1:1 A year using `BTE', nogen
merge 1:1 A year using `BTTI', nogen
merge 1:1 A year using `StateControl', nogen
merge 1:1 A year using `PMR', nogen
merge 1:1 A year using `OutputGap', nogen
merge 1:1 A year using `population', nogen
merge 1:1 A year using `netreplacementrate', nogen

drop if missing(A)
sort A year



rename A Country

qui foreach var of varlist AverageTenure-GDPgrowth BTE-NetReplacementRate {
	gen `var'_real=real(`var')
	drop `var'
	rename `var'_real `var'
}

***************************************************************************************
*USE THE POPULATION WEIGHTED CROSS-COUNTRY MEANS
drop if Country=="United States, Canada" | Country=="European Economic Area" | Country=="Western Europe" | Country=="OECD countries"

rename Country country
*use the weighted means
append using "$data\crosscountry_means.dta"
rename country Country
sort Country year


*SET CROSS-COUNTRY INSTITUTIONS TO MISSING IF WE DON'T INCLUDE THEM IN MAIN SPECIFICATION
foreach inst of varlist AverageTenure-NetReplacementRate {
replace `inst'=. 		if inlist(Country,"United States, Canada","European Economic Area","OECD countries","Western Europe")
}
***************************************************************************************

*Label variables
label variable ALMPExp "Public expenditure ALMP" 
label variable AverageTenure "Average tenure"
label variable CollBarg "Collective bargaining coverage"
label variable StrictEmpColl "EPL (collective)"
label variable StrictEmpIndColl "EPL (individual and collective)"
label variable StrictEmpInd "EPL"
label variable StrictEmpTemp "EPL (temporary)"
label variable TradeUnionDens "Trade union density"
label variable MintoMedianWage "Min./median wage"
label variable MinToMeanWage "Min./mean wage"
label variable NetReplacementRate "Net replacement rate"
label variable Unemployment "Unemployment rate"
label variable OutputGap "Output gap"
label variable GDPgrowth "GDP growth"
label variable BTE "Barriers to entrepreneurship"
label variable BTTI "Barriers to trade and investment"
label variable StateControl "State control of businesses"
label variable PMR "Product market regulation"

*Rescale collective bargaining coverage
replace CollBarg=CollBarg/100
replace NetReplacementRate=NetReplacementRate/100


order Country year AverageTenure CollBarg StrictEmpColl StrictEmpIndColl StrictEmpInd StrictEmpTemp TradeUnionDens MintoMedianWage MinToMeanWage Unemployment GDPgrowth ALMPExp BTE BTTI StateControl PMR OutputGap NetReplacementRate population


save "$data/OECD_panel.dta", replace emptyok


**************************************************************************************
*2: Create OECD paneldata only containing the years matched to our database of estimates*
**************************************************************************************

clear all
use "$data\Database_estimates_all.dta"
rename Country country

*Rescale collective bargaining coverage
replace CollBarg=CollBarg/100
replace NetReplacementRate=NetReplacementRate/100



*Drop papers not in English, not published nor forthcoming
drop if authors=="DØR"
drop if authors=="Malchow-Møller, Munch and Skaksen" & year==2008
*check this
drop if wp=="1"
drop if wp=="1" | wp=="Don't include"


*DROP SIMULATION EFFECT SIZES AND FIRM, INDUSTRY LEVEL VARIATION
drop if method=="simulation" | inlist(variation,"structural","Other","t","jt","it","i","iakt")

*Replace OECD-variables with missing if it's matched more than 5 years after the first year in the data period
gen firstyear=substr(data_period,1,4)
destring firstyear, replace

foreach var in ALMPExp AverageTenure CollBarg ///
StrictEmpIndColl StrictEmpInd StrictEmpColl StrictEmpTemp TradeUnionDens /// 
Unemployment OutputGap GDPgrowth MintoMedianWage MinToMeanWage NetReplacementRate BTE BTTI StateControl PMR {
gen diff_`var'=`var'_year-firstyear
}


*replace oecd-variables with missing if they are measured more than 5 years after start of the data period
foreach var in ALMPExp AverageTenure CollBarg ///
StrictEmpIndColl StrictEmpInd StrictEmpColl StrictEmpTemp TradeUnionDens /// 
Unemployment OutputGap GDPgrowth MintoMedianWage MinToMeanWage NetReplacementRate BTE BTTI StateControl PMR {
replace `var'=. if diff_`var' > 5 | diff_`var' < -5
}

drop diff_ALMPExp-diff_PMR Native_low_2010-Foreign_high_2000 data_period-Euclid 


*SET CROSS-COUNTRY INSTITUTIONS TO MISSING IF WE DON'T INCLUDE THEM IN MAIN SPECIFICATION
foreach inst of varlist AverageTenure-NetReplacementRate {
replace `inst'=. 		if inlist(country,"United States, Canada","European Economic Area","OECD countries","Western Europe")
replace `inst'_avg=. 	if inlist(country,"United States, Canada","European Economic Area","OECD countries","Western Europe")
}


preserve
keep country 
duplicates drop
gen length=2018-1960
expand length
bysort country: gen dat_year=1960+_n-1
drop length
duplicates drop
save "$data\OECD_panel_years_used.dta", replace
restore

foreach var of varlist AverageTenure-NetReplacementRate {
preserve
rename `var'_year dat_year
collapse (mean) `var', by(country dat_year)
merge 1:1 country dat_year using "$data\OECD_panel_years_used.dta"
drop _merge
sort country dat_year
drop if dat_year==.
label variable dat_year "Year of measurement for institution"
save "$data\OECD_panel_years_used.dta", replace
restore
}



**************************************************************************************
*3: Create OECD paneldata only containing the COUNTRIES matched to our database of estimates*
**************************************************************************************

clear all
use "$data\Database_estimates_all.dta"
rename Country country

*Rescale collective bargaining coverage
replace CollBarg=CollBarg/100
replace NetReplacementRate=NetReplacementRate/100



*Drop papers not in English, not published nor forthcoming
drop if authors=="DØR"
drop if authors=="Malchow-Møller, Munch and Skaksen" & year==2008
*check this
drop if wp=="1"
drop if wp=="1" | wp=="Don't include"


*DROP SIMULATION EFFECT SIZES AND FIRM, INDUSTRY LEVEL VARIATION
drop if method=="simulation" | inlist(variation,"structural","Other","t","jt","it","i","iakt")


drop Native_low_2010-Foreign_high_2000 data_period-Euclid 

*SET CROSS-COUNTRY INSTITUTIONS TO MISSING IF WE DON'T INCLUDE THEM IN MAIN SPECIFICATION
foreach inst of varlist AverageTenure-NetReplacementRate {
replace `inst'=. 		if inlist(country,"United States, Canada","European Economic Area","OECD countries","Western Europe")
replace `inst'_avg=. 	if inlist(country,"United States, Canada","European Economic Area","OECD countries","Western Europe")
}

preserve
keep country 
duplicates drop
save "$data\OECD_panel_countries_used.dta", replace
restore

clear all
use "$data/OECD_panel.dta"
rename Country country

merge m:1 country using "$data\OECD_panel_countries_used.dta"
drop if _merge==1
drop if _merge==2
drop _merge

label variable year "Year of measurement for institution"



*Country averages
save "$data\OECD_panel_countries_used.dta", replace

collapse (mean) AverageTenure CollBarg StrictEmpColl StrictEmpIndColl StrictEmpInd StrictEmpTemp ///
TradeUnionDens MintoMedianWage MinToMeanWage Unemployment GDPgrowth ALMPExp BTE BTTI StateControl PMR ///
OutputGap NetReplacementRate, by(country)

foreach var of varlist AverageTenure-NetReplacementRate {
rename `var' `var'_avg
}


*Standardize variables and generate dummies for above median rigidity
foreach x in StrictEmpInd_avg StrictEmpColl_avg AverageTenure_avg CollBarg_avg NetReplacementRate_avg {
sum `x' if !inlist(country,"United States, Canada","European Economic Area","OECD countries","Western Europe")
gen z_`x'=(`x'-r(mean))/r(sd)

egen _median_`x'=median(`x')
gen d_`x'=0 if `x'!=.
replace d_`x'=1 if `x'!=. & `x' >= _median_`x'
label var d_`x' "Above Median `x'"
drop _median_`x'
}





save "$data\OECD_means_2.dta", replace emptyok

clear 
use "$data\OECD_panel_countries_used.dta"

merge m:1 country using "$data\OECD_means_2.dta"
drop if _merge==2
drop _merge



**********************************************************

*INDEX OF STANDARIZED INSTITUTION VALUES IF ALL ARE NON-MISSING
*Take mean of standardized values if all institutions are non-missing
alpha z_StrictEmpInd_avg z_StrictEmpColl_avg z_AverageTenure_avg z_CollBarg_avg z_NetReplacementRate_avg if z_StrictEmpInd_avg!=. & z_StrictEmpColl_avg!=.  & z_AverageTenure_avg!=. & z_CollBarg_avg!=. & z_NetReplacementRate_avg!=., gen(index)
label var index "\emph{Index}"


**********************************************************
*PCA OF STANDARIZED INSTITUTION VALUES IF ALL INSTITUTION VALUES ARE NON-MISSING
pca z_StrictEmpInd_avg z_StrictEmpColl_avg z_AverageTenure_avg z_CollBarg_avg z_NetReplacementRate_avg if z_StrictEmpInd_avg!=. & z_StrictEmpColl_avg!=.  & z_AverageTenure_avg!=. & z_CollBarg_avg!=. & z_NetReplacementRate_avg!=.

*Check principal components


*Save first principal component
predict pc1, score 
label var pc1 "\emph{Index (PCA)}"

preserve
duplicates drop country, force
save "$data\OECD_means_2.dta", replace emptyok
restore

save "$data\OECD_panel_countries_used.dta", replace





